using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using MM.Entities;

namespace MM.Business
{
    class clsTAI_KHOAN
    {
        public clsTAI_KHOAN(string sConn)
	    {
		    SqlHelper.ConnectionString = sConn;
	    }
        public List<TAI_KHOAN> DataTableToLstObj(DataTable dt)
        {
            TAI_KHOAN taiKhoanDTO;
            List<TAI_KHOAN> lstTaiKhoanDTO = new List<TAI_KHOAN>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                taiKhoanDTO = new TAI_KHOAN();
                try
                {
                    taiKhoanDTO._maTaiKhoan = int.Parse(dt.Rows[i]["MaTaiKhoan"].ToString());
                }
                catch (Exception ex)
                {
                }
                try
                {
                    taiKhoanDTO._hoTen = dt.Rows[i]["HoTen"].ToString();
                }
                catch (Exception ex)
                {
                }
                try
                {
                    taiKhoanDTO._soCMND = dt.Rows[i]["SoCMND"].ToString();
                }
                catch (Exception ex)
                {
                }
                try
                {
                    taiKhoanDTO._matKhau = dt.Rows[i]["MatKhau"].ToString();
                }
                catch (Exception ex)
                {
                }
                try
                {
                    taiKhoanDTO._maTau = dt.Rows[i]["MaTau"].ToString();
                }
                catch (Exception ex)
                {
                }
                try
                {
                    taiKhoanDTO._tinhTrang = int.Parse(dt.Rows[i]["TinhTrang"].ToString());
                }
                catch (Exception ex)
                {
                } 
                lstTaiKhoanDTO.Add(taiKhoanDTO);
            }
            return lstTaiKhoanDTO;
        }

        public TAI_KHOAN DataTableToObj(DataTable dt)
        {
            TAI_KHOAN taiKhoanDTO = new TAI_KHOAN();
            try
            {
                taiKhoanDTO._maTaiKhoan = int.Parse(dt.Rows[0]["MaTaiKhoan"].ToString());
            }
            catch (Exception ex)
            {
            }
            try
            {
                taiKhoanDTO._hoTen = dt.Rows[0]["HoTen"].ToString();
            }
            catch (Exception ex)
            {
            }
            try
            {
                taiKhoanDTO._soCMND = dt.Rows[0]["SoCMND"].ToString();
            }
            catch (Exception ex)
            {
            }
            try
            {
                taiKhoanDTO._matKhau = dt.Rows[0]["MatKhau"].ToString();
            }
            catch (Exception ex)
            {
            }
            try
            {
                taiKhoanDTO._maTau = dt.Rows[0]["MaTau"].ToString();
            }
            catch (Exception ex)
            {
            }
            try
            {
                taiKhoanDTO._tinhTrang = int.Parse(dt.Rows[0]["TinhTrang"].ToString());
            }
            catch (Exception ex)
            {
            }

            return taiKhoanDTO;
        }

        public List<TAI_KHOAN> LoadTaiKhoanByTau(string maTau)
        {
            DataTable dt = new DataTable();
            List<TAI_KHOAN> lstTaiKhoan = new List<TAI_KHOAN>();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTAI_KHOANByTau";
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaTau", maTau);
                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lstTaiKhoan = DataTableToLstObj(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lstTaiKhoan;
        }

        public List<TAI_KHOAN> LoadTaiKhoanByTauAllTinhTrang(string maTau)
        {
            DataTable dt = new DataTable();
            List<TAI_KHOAN> lstTaiKhoan = new List<TAI_KHOAN>();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTAI_KHOANByTauAllTinhTrang";
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaTau", maTau);
                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lstTaiKhoan = DataTableToLstObj(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lstTaiKhoan;
        }

        public TAI_KHOAN LoadTaiKhoan(int maTaiKhoan)
        {
            DataTable dt = new DataTable();
            TAI_KHOAN taiKhoanDTO = new TAI_KHOAN();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTAI_KHOAN";
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaTaiKhoan", maTaiKhoan);
                dt = SqlHelper.ExecuteToDataTable(spName, par);
                taiKhoanDTO = DataTableToObj(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return taiKhoanDTO;
        }

        public TAI_KHOAN LoadTaiKhoanAllTinhTrang(int maTaiKhoan)
        {
            DataTable dt = new DataTable();
            TAI_KHOAN taiKhoanDTO = new TAI_KHOAN();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTAI_KHOANAllTinhTrang";
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaTaiKhoan", maTaiKhoan);
                dt = SqlHelper.ExecuteToDataTable(spName, par);
                taiKhoanDTO = DataTableToObj(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return taiKhoanDTO;
        }

        public TAI_KHOAN LoadTaiKhoanByCMNDVaMatKhau(string soCMND, string matKhau, string maTau)
        {
            DataTable dt = new DataTable();
            TAI_KHOAN taiKhoanDTO = new TAI_KHOAN();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTAI_KHOANByCMNDAndMatKhau";
                SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@SoCMND", soCMND);
                par[1] = new SqlParameter("@MatKhau", matKhau);
                par[2] = new SqlParameter("@MaTau", maTau);
                dt = SqlHelper.ExecuteToDataTable(spName, par);
                taiKhoanDTO = DataTableToObj(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return taiKhoanDTO;
        }

        public int InsertTAI_KHOAN(string hoTen, string soCMND, string matKhau, string maTau, int tinhTrang)
        {
            int result = 0;
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_InsertUpdateTAI_KHOAN";
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@HoTen", hoTen);
                par[1] = new SqlParameter("@SoCMND", soCMND);
                par[2] = new SqlParameter("@MatKhau", matKhau);
                par[3] = new SqlParameter("@MaTau", maTau);
                par[4] = new SqlParameter("@TinhTrang", tinhTrang);
                result = SqlHelper.ExecuteNonQuery(spName, par);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return result;
        }

        public int UpdateTAI_KHOAN(string maTaiKhoan, string hoTen, string soCMND, string maTau, int tinhTrang)
        {
            int result = 0;
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_UpdateTAI_KHOAN";
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@HoTen", hoTen);
                par[1] = new SqlParameter("@SoCMND", soCMND);
                par[2] = new SqlParameter("@MaTau", maTau);
                par[3] = new SqlParameter("@TinhTrang", tinhTrang);
                par[4] = new SqlParameter("@MaTaiKhoan", maTaiKhoan);
                result = SqlHelper.ExecuteNonQuery(spName, par);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return result;
        }

        public int UpdateMatKhau(int maTaiKhoan, string soCMND, string matKhauCu, string matKhauMoi)
        {
            int result = 0;
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_UpdateMatKhau";
                SqlParameter[] par = new SqlParameter[4];
                par[0] = new SqlParameter("@MaTaiKhoan", maTaiKhoan);
                par[1] = new SqlParameter("@SoCMND", soCMND);
                par[2] = new SqlParameter("@MatKhauCu", matKhauCu);
                par[3] = new SqlParameter("@MatKhauMoi", matKhauMoi);
                result = SqlHelper.ExecuteNonQuery(spName, par);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return result;
        }

        public int ResetMatKhau(int maTaiKhoan)
        {
            int result = 0;
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_ResetMatKhau";
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaTaiKhoan", maTaiKhoan);
                result = SqlHelper.ExecuteNonQuery(spName, par);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return result;
        }

    }
}
